# -*- coding: utf-8 -*-

from datetime import datetime

from sqlalchemy import *

from sqlalchemy.orm import *

import time, random, md5

import os
import sys

from time import time, localtime, strftime


"""
class: Relationships
"""
class Relationships(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Tables
"""
class Tables(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Models
"""
class Models(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Columns
"""
class Columns(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Column_types
"""
class Column_types(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Dbs
"""
class Dbs(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Dictionarys
"""
class Dictionarys(object):
    """
    
    """
    def __init__(self):
        pass

"""
class: Projects
"""
class Projects(object):
    """
    
    """
    def __init__(self):
        pass


class DB:

    def __init__(self, uri, echo = False):
        print "create engine"
        self.err_count = 0
        self.engine = create_engine(uri,encoding='utf-8')
        metadata = MetaData(self.engine)
        metadata.bind.echo  = echo

        
        """
        taqble: relationships
        ForeignKey('users.user_id')
        """
        self.relationships = Table('relationships', metadata,
            Column('pk_relationship_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('fk_table_id', Integer, nullable=True ), 
            Column('fk_table_id2', Integer, nullable=True ), 
            Column('project', String(30), nullable=False ), 
            Column('ctable', String(30), nullable=False ), 
            Column('ptable', String(30), nullable=False ), 
            Column('relation_type', String(30), nullable=False ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: tables
        ForeignKey('users.user_id')
        """
        self.tables = Table('tables', metadata,
            Column('pk_table_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('project', String(30), nullable=False ), 
            Column('entity', String(30), nullable=False ), 
            Column('table_type', String(30), nullable=False ), 
            Column('name', String(30), nullable=False ), 
            Column('note', String(2000), nullable=True ), 
            Column('xpos', Integer, nullable=True ), 
            Column('ypos', Integer, nullable=True ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: models
        ForeignKey('users.user_id')
        """
        self.models = Table('models', metadata,
            Column('pk_model_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('fkp_model_id', Integer, nullable=True ), 
            Column('name', String(30), nullable=False ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: columns
        ForeignKey('users.user_id')
        """
        self.columns = Table('columns', metadata,
            Column('pk_column_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('fk_table_id', Integer, nullable=True ), 
            Column('project', String(30), nullable=False ), 
            Column('name', String(30), nullable=False ), 
            Column('attribute', String(30), nullable=False ), 
            Column('column_type', String(30), nullable=False ), 
            Column('tablename', String(30), nullable=False ), 
            Column('note', String(2000), nullable=True ), 
            Column('data_type', String(30), nullable=False ), 
            Column('width', SmallInteger, nullable=True ), 
            Column('scale', SmallInteger, nullable=False ), 
            Column('not_null', SmallInteger, nullable=True ), 
            Column('default_value', String(60), nullable=True ), 
            Column('ispk', SmallInteger, nullable=True ), 
            Column('isfk', SmallInteger, nullable=True ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: column_types
        ForeignKey('users.user_id')
        """
        self.column_types = Table('column_types', metadata,
            Column('pk_column_type_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('fk_db_id', Integer, nullable=True ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: dbs
        ForeignKey('users.user_id')
        """
        self.dbs = Table('dbs', metadata,
            Column('pk_db_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('name', String(30), nullable=False ), 
            Column('version', String(30), nullable=False ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: dictionarys
        ForeignKey('users.user_id')
        """
        self.dictionarys = Table('dictionarys', metadata,
            Column('pk_dictionary_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('fk_model_id', Integer, nullable=True ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        
        """
        taqble: projects
        ForeignKey('users.user_id')
        """
        self.projects = Table('projects', metadata,
            Column('pk_project_id', Integer, primary_key=True, autoincrement=True, nullable=False), 
            Column('name', String(30), nullable=False ), 
            Column('createdby', String(30), nullable=False ), 
            Column('createdon', DateTime, nullable=False ,default = datetime.now()), 
            Column('lastupdatedby', String(30), nullable=True ), 
            Column('lastupdatedon', DateTime, nullable=True ), 
            Column('active', SmallInteger, nullable=False ), 
            Column('ordering', Integer, nullable=False ), 
            Column('hits', Integer, nullable=False ), 
            Column('project', String(36), nullable=False, default='kylin')
        )
        metadata.create_all()
        
        """
        mapper
        """
        mapper(Relationships,self.relationships)
        mapper(Tables,self.tables)
        mapper(Models,self.models)
        mapper(Columns,self.columns)
        mapper(Column_types,self.column_types)
        mapper(Dbs,self.dbs)
        mapper(Dictionarys,self.dictionarys)
        mapper(Projects,self.projects)

    def execute(self, sql):
        sql = sql.decode('GBK')
        connection = self.engine.connect()
        try:
            result = connection.execute(sql)
            return result
        except Exception, e:
            self.err_count = self.err_count + 1
            print sql.encode('utf8')
            print e
            if self.err_count > 6:
                sys.exit(0)
    
    def exists(self):
        sql = "select name from projects where name = 'kylin'"
        result = self.execute(sql)
        value = result.fetchone()
        if value != None:
            if value[0] == 'kylin':
                print "project already exists"
                sys.exit(0)
        
    def insert(self):
        now = "'%s'" %(strftime("%Y-%m-%d %H:%M:%S", localtime()))
        sql = "insert into projects (name, createdby, createdon, active, ordering, hits, project) values('kylin', 'admin', %s, 1,1,0,'kylin')" %now
        self.execute(sql)

    def source(self, source):
        file = open(source,'r')    
        i = 0
        sqlp = []
        j = 0
        for line in file:
          i = i +1
          line = line.strip()
          now = strftime("%Y-%m-%d %H:%M:%S", localtime())
          now = "'%s'" % now

          line = line.replace('now()',now)
          if line[-2:] ==');':
              sqlp.append(line)
              mysql = ''
              for sql in sqlp:
                  mysql += sql
              #print mysql #.decode('utf8')
              j = j +1
              if j >3:
                  pass
              self.execute(mysql)
              #db.execute('PRAGMA table_info("models")')
              #print "\n-----------\n"
              sqlp = []
              
              #print line, i
          else:
            sqlp.append(line)

    def select(self):        
        s=self.projects.select(self.projects.c.name == 'kylin')
        print s
        r = s.execute()
        print r.fetchone()
        pass

    def buildfk2(self):
        
        """
        select pk_table_id from tables
        id = 
        update columns set fk_table_id = id
        
        """
        pass
        
    def buildfk(self):
        sql = """
            UPDATE COLUMNS col SET col.fk_table_id = ( SELECT tab.pk_table_id
            FROM TABLES tab
            WHERE tab.name = col.tablename ) ;
            """
        print sql
        
def test():
    uri = 'sqlite:///data02.dm1'
    #uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/mymodel?charset=utf8&use_unicode=0'

    db = DB(uri, False)
    db.exists()    
    db.source('metadata.sql')
    db.insert()

def getMeta():
    uri = 'sqlite:///data.md1'
    #uri = 'mysql://root:myjsy03bjwqt@127.0.0.1:3336/mymodel?charset=utf8&use_unicode=0'
    db = DB(uri, False)
    db.select()

if __name__ == "__main__":
    test()
    pass
    #getMeta()
